How to: Create an append query to add records to a table from another table.
Solution:
Create a new query in Design view, add the desired fields to the design grid, select the 'Query' menu and select 'Append...', type a table to which to append the data, and run the query.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' from the list box.
5) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
6) Do one of the following:
a) Click the 'Tables' tab to display a list of tables only.
b) Click the 'Queries' tab to display a list of queries only.
c) Click the 'Both' tab to display a list of both tables and queries.
7) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
8) Click 'Add'.
9) Click 'Close' when all of the desired tables and/or queries have been added.
10) Select the 'Query' menu and select 'Append...'. (The Append dialog box appears.)
11) In the 'Table Name' box in the 'Append To' group, type the name of the table to which to append the fields in the design grid.
12) Do one of the following:
a) If the table to which to append the data is in the open database, select the 'Current Database' radio button.
b) If the table to which to append the data is in a different database:
1] Select the 'Another Database' radio button.
2] Type the path and filename of the other database in the 'File Name' box.
13) Click 'OK'.
14) Add fields that are to be appended to another table to the query design grid:
a) To add all the fields in one table in the Query Design window:
1] Select the asterisk (*) in the desired table field list in the top half of the Query Design window.
2] Drag the asterisk (*) to the design grid in the bottom half of the Query Design window.
b) Select the desired field from a table field list in the top half of the Query Design window. (All the fields for that table are included in the append.)
Design grid
c) Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
d) Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
e) Repeat steps 14)a) through 14)d) for each field to append to another table.
NOTE: Fields can also be added by selecting a field from the drop-down list box in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
15) (Special Case) If the fields to be appended and the fields in the table to append to have different names:
a) Type the names of each corresponding field in the table appending to in the 'Append To' row.
b) Repeat step 15)a) for each field in the query design grid with a different name than in the append table being created.
16) To preview the new table with the appended data BEFORE creating the append table, select the 'View' menu and select 'Datasheet'.
17) To append the data, creating a new table:
a) Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
b) Select the 'Query' menu and select 'Run'. (The table is created and is added to list of tables in the Database window.)
18) To save the query:
a) Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
b) Type a name for the append query in the 'Query Name' box.
c) Click 'OK'.
19) Select the 'File' menu and select 'Close' to close the Query Design view.
